﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Linq;

namespace MeTool.Sql
{
    /// <summary>
    /// 本类提供包装SQL语句的静态方法(其中的许多方法都是SqlServer专用的)
    /// </summary>
    /// <remarks></remarks>
    public static class SqlHelper
    {
        /// <summary>
        /// 组织并返回一个Select Count(*)的SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string GetCount(string tableName, string where = "")
        {
            return "select count(*) from " + tableName + 
                (where.IsEmpty() ? "" : " where " + where) + " ";
        }

        /// <summary>
        /// 组织并返回一个Delete的SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string GetDeleteBase(string tableName, string where = "")
        {
            if (where.IsNotEmpty() && !where.Trim().ToLower().StartsWith("where"))
            {
                where = " where " + where;
            }
            return "delete " + tableName + where + "; ";
        }

        /// <summary>
        /// 生成并返回一段Select的SQL语句. 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="returnList"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <param name="topCount"></param>
        /// <param name="isDistinct"></param>
        /// <returns></returns>
        public static string GetSelectBase(string tableName, string returnList = "", string where = "", string order = "", int topCount = -1, bool isDistinct = false)
        {
            string dText = "select ";
            if (isDistinct) dText += "distinct ";
            if (topCount >= 0) dText += "top " + topCount.ToString() + " ";
            dText += returnList.IsEmpty() ? "*" : returnList;
            dText += " from " + tableName;
            if (where.IsNotEmpty()) dText += " where " + where;
            if (order.IsNotEmpty()) dText += " order by " + order;
            return dText + " ";
        }

        /// <summary>
        /// 组织并返回一个 Select 的 SQL 语句, 此语句不包含 From 及以后部分, 仅包含返回的值列表, 属性或字段名做为列名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public static string SelectNoTable<T>(T sqlList) where T : class
        {
            var dProps = sqlList.GetType().GetMembers(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.GetField);
            if (dProps.Length == 0) return "";
            var dPropList = dProps.ToList();

            dPropList.RemoveAll(item => !(item is PropertyInfo || item is FieldInfo));
            object GetMemberValue(MemberInfo info)
            {
                if (info is PropertyInfo prop) return prop.GetValue(sqlList, null); else return ((FieldInfo)info).GetValue(sqlList);
            }
            var dSql = "select ";
            dSql += dPropList.Aggregate("", (str, item) => (str == "" ? "" : str + ",") + Helper.GetSqlValue(GetMemberValue(item)) + " " + item.Name);
            return dSql;
        }

        /// <summary>
        /// 获取一段SQL代码. 意义为: 返回指定表指定条件及排序方式找到的第一行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public static string GetRowFirst(string tableName, string where = "", string order = "")
        {
            return "select top (1) * from " + tableName +
                (where.IsEmpty() ? "" : " where " + where) +
                (order.IsEmpty() ? "" : " order by " + order) + " ";
        }

        /// <summary>
        /// 生成的Sql语句的意义: 当指定的Select执行后存在内容时,执行TrueThen,否则执行FalseThen
        /// <para>结尾带分号</para>
        /// </summary>
        /// <param name="existSelect"></param>
        /// <param name="trueThen"></param>
        /// <param name="falseThen"></param>
        /// <returns></returns>
        public static string IfExists(string existSelect, string trueThen, string falseThen = "")
        {
            string dText = "if exists(" + existSelect + ") begin " + trueThen + " end ";
            if (falseThen.IsNotEmpty()) dText += "else begin " + falseThen + " end ";
            return dText + "; ";
        }

        /// <summary>
        /// 生成的Sql语句的意义: 当指定的Select执行后不存在内容时,执行TrueThen,否则执行FalseThen
        /// <para>结尾带分号</para>
        /// </summary>
        /// <param name="existSelect"></param>
        /// <param name="trueThen"></param>
        /// <param name="falseThen"></param>
        /// <returns></returns>
        public static string IfNotExists(string existSelect, string trueThen, string falseThen = "")
        {
            string dText = "if not exists(" + existSelect + ") begin " + trueThen + " end";
            if (falseThen.IsNotEmpty()) dText = dText + " else begin " + falseThen + " end";
            return dText + "; ";
        }

        /// <summary>
        /// 抛出异常信息并返回 的SQL指令
        /// <para>结尾带分号</para>
        /// </summary>
        /// <param name="errorText"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetRaiserror(string errorText)
        {
            return "raiserror('" + errorText.Replace("'", "") + "',16,1);return; ";
        }

        /// <summary>
        /// 抛出异常信息并返回 的SQL指令
        /// <para>结尾带分号</para>
        /// </summary>
        /// <param name="errorText"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string Raiserror(string errorText)
        {
            return "raiserror('" + errorText.Replace("'", "") + "',16,1);return; ";
        }

        /// <summary>
        /// 当指定Select存在内容时,抛出异常信息并返回
        /// </summary>
        /// <param name="existSelect"></param>
        /// <param name="errorText"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string IfExistRaiserror(string existSelect, string errorText)
        {
            existSelect = existSelect.Trim();
            if (existSelect.EndsWith(";")) existSelect = existSelect.Substring(0, existSelect.Length - 1);
            return "if exists(" + existSelect + ") begin " + GetRaiserror(errorText) + "end ";
        }

        /// <summary>
        /// 当指定的Select执行后不存在内容时,抛出异常信息并返回
        /// </summary>
        /// <param name="existSelect"></param>
        /// <param name="errorText"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string IfNotExistRaiserror(string existSelect, string errorText)
        {
            existSelect = existSelect.Trim();
            if (existSelect.EndsWith(";")) existSelect = existSelect.Substring(0, existSelect.Length - 1);
            return "if not exists(" + existSelect + ") begin " + GetRaiserror(errorText) + "end ";
        }

        /// <summary>
        /// 判断指令的包装
        /// </summary>
        /// <param name="where"></param>
        /// <param name="trueSqlText"></param>
        /// <param name="falseSqlText"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string IfThen(string where, string trueSqlText, string falseSqlText = "")
        {
            string dText = "if " + where + " begin " + trueSqlText + " end";
            if (falseSqlText.IsNotEmpty()) dText += " else begin " + falseSqlText + " end";
            return dText + " ";
        }

        /// <summary>
        /// 当SQL表达式成立时,抛出异常信息并返回
        /// </summary>
        /// <param name="where"></param>
        /// <param name="errorText"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string IfRaiserror(string where, string errorText)
        {
            return "if " + where + " begin " + GetRaiserror(errorText) + " end ";
        }

        /// <summary>
        /// 生成一段While循环的Sql语句
        /// </summary>
        /// <param name="where"></param>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static string While(string where, string sqlText)
        {
            return "while(" + where + ") begin " + sqlText + " end;";
        }

        /// <summary>
        /// 把 DataTable 的内容转成 List&lt;obj&gt;
        /// </summary>
        /// <typeparam name="obj">必须是有默认构造函数的对象类型</typeparam>
        /// <param name="sourceTable">要转换的数据</param>
        /// <param name="returnList">承接数据的列表</param>
        /// <param name="objectCreated">可以在这里为每一行对象实例做二次处理</param>
        public static void TableToList<obj>(this DataTable sourceTable, IList<obj> returnList, Action<obj> objectCreated = null) where obj : class, new()
        {
            var dActionList = new List<Action<DataRow, obj>>();
            var dColumnCount = 0;

            for (var xx = 0; xx < sourceTable.Columns.Count; xx++)
            {
                var dColumnName = sourceTable.Columns[xx].ColumnName;
                var dMembers = typeof(obj).GetMember(dColumnName, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public | BindingFlags.GetField | BindingFlags.GetProperty);
                if (dMembers.Length == 0) continue;

                var dIndex = xx;
                if (dMembers[0].MemberType == MemberTypes.Field)
                {
                    var dField = (FieldInfo)dMembers[0];
                    dActionList.Add((theRow, theObj) =>
                    {
                        var dValue = theRow[dIndex];
                        if (dValue != DBNull.Value)
                        {
                            if (dField.FieldType.IsGenericType)
                            {
                                dField.SetValue(theObj, Convert.ChangeType(dValue, dField.FieldType.GetGenericArguments()[0]));
                            }
                            else
                            {
                                dField.SetValue(theObj, Convert.ChangeType(dValue, dField.FieldType));
                            }
                        }
                    });
                    dColumnCount++;
                }
                else
                {
                    var dProp = (PropertyInfo)dMembers[0];
                    dActionList.Add((theRow, theObj) =>
                    {
                        var dValue = theRow[dIndex];
                        if (dValue != DBNull.Value)
                        {
                            if (dProp.PropertyType.IsGenericType)
                            {
                                dProp.SetValue(theObj, Convert.ChangeType(dValue, dProp.PropertyType.GetGenericArguments()[0]), null);
                            }
                            else
                            {
                                dProp.SetValue(theObj, Convert.ChangeType(dValue, dProp.PropertyType), null);
                            }
                        }
                    });
                    dColumnCount++;
                }
            }
            foreach (DataRow dRow in sourceTable.Rows)
            {
                obj dObj = new obj();
                dActionList.ForEach(dAction => dAction(dRow, dObj));
                returnList.Add(dObj);
                objectCreated?.Invoke(dObj);
            }
        }
    }
}
